/**
 * Copyright &copy; 2012-2016 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved.
 */
package com.better.util.excelUtil;

import com.better.backend.mqReceiverService.questionReceiver.model.PointPaperModel;
import com.better.backend.mqReceiverService.questionReceiver.model.QuestionModel;
import com.better.backend.mqReceiverService.studentRecever.model.ScoreReceverModel;
import com.better.backend.mqReceiverService.studentRecever.model.StuScoreReceverModel;
import com.better.common.base.ServiceResponse;
import com.better.common.utils.Encodes;
import com.better.common.utils.GlobalUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**
 * 导入Excel文件（支持“XLS”和“XLSX”格式）
 * @author ThinkGem
 * @version 2013-03-10
 */
public class ImportExcel {
	
	private static Logger log = LoggerFactory.getLogger(ImportExcel.class);
			
	/**
	 * 工作薄对象
	 */
	private Workbook wb;
	
	/**
	 * 工作表对象
	 */
	private Sheet sheet;
	
	/**
	 * 标题行号
	 */
	private int headerNum;
	
	/**
	 * 构造函数
	 * @param path 导入文件，读取第一个工作表
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(String fileName, int headerNum) 
			throws InvalidFormatException, IOException {
		this(new File(fileName), headerNum);
	}
	
	/**
	 * 构造函数
	 * @param path 导入文件对象，读取第一个工作表
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(File file, int headerNum) 
			throws InvalidFormatException, IOException {
		this(file, headerNum, 0);
	}

	/**
	 * 构造函数
	 * @param path 导入文件
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @param sheetIndex 工作表编号
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(String fileName, int headerNum, int sheetIndex) 
			throws InvalidFormatException, IOException {
		this(new File(fileName), headerNum, sheetIndex);
	}
	
	/**
	 * 构造函数
	 * @param path 导入文件对象
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @param sheetIndex 工作表编号
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(File file, int headerNum, int sheetIndex) 
			throws InvalidFormatException, IOException {
		this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);
	}
	
	/**
	 * 构造函数
	 * @param file 导入文件对象
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @param sheetIndex 工作表编号
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex) 
			throws InvalidFormatException, IOException {
		this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);
	}

	/**
	 * 构造函数
	 * @param path 导入文件对象
	 * @param headerNum 标题行号，数据行号=标题行号+1
	 * @param sheetIndex 工作表编号
	 * @throws InvalidFormatException 
	 * @throws IOException 
	 */
	public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex) 
			throws InvalidFormatException, IOException {
		if (StringUtils.isBlank(fileName)){
			throw new RuntimeException("导入文档为空!");
		}else if(fileName.toLowerCase().endsWith("xls")){    
			this.wb = new HSSFWorkbook(is);    
        }else if(fileName.toLowerCase().endsWith("xlsx")){  
        	this.wb = new XSSFWorkbook(is);
        }else{  
        	throw new RuntimeException("文档格式不正确!");
        }  
		if (this.wb.getNumberOfSheets()<sheetIndex){
			throw new RuntimeException("文档中没有工作表!");
		}
		this.sheet = this.wb.getSheetAt(sheetIndex);
		this.headerNum = headerNum;
		log.debug("Initialize success.");
	}
	
	/**
	 * 获取行对象
	 * @param rownum
	 * @return
	 */
	public Row getRow(int rownum){
		return this.sheet.getRow(rownum);
	}

	/**
	 * 获取数据行号
	 * @return
	 */
	public int getDataRowNum(){
		return headerNum+1;
	}
	
	/**
	 * 获取最后一个数据行号
	 * @return
	 */
	public int getLastDataRowNum(){
		return this.sheet.getLastRowNum()+headerNum;
	}
	
	/**
	 * 获取最后一个列号
	 * @return
	 */
	public int getLastCellNum(){
		return this.getRow(headerNum).getLastCellNum();
	}
	
	/**
	 * 获取单元格值
	 * @param row 获取的行
	 * @param column 获取单元格列号
	 * @return 单元格值
	 */
	public Object getCellValue(Row row, int column){
		Object val = "";
		try{
			Cell cell = row.getCell(column);
			if (cell != null){
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
					DecimalFormat df = new DecimalFormat("0");
					String whatYourWant = df.format(cell.getNumericCellValue());
					val = whatYourWant;
				}else if (cell.getCellType() == Cell.CELL_TYPE_STRING){
					val = cell.getStringCellValue();
				}else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){
					val = cell.getCellFormula();
				}else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
					val = cell.getBooleanCellValue();
				}else if (cell.getCellType() == Cell.CELL_TYPE_ERROR){
					val = cell.getErrorCellValue();
				}
			}
		}catch (Exception e) {
			return val;
		}
		return val;
	}

	/**
	 * 导入试卷
	 * @return
	 */
	public List<QuestionModel> getQues(Map<String,PointPaperModel> poList) throws Exception{
		List<QuestionModel> reList = new ArrayList<>();
		int qno = 1;
		//遍历数据
		for (int i = this.getDataRowNum(); i <= this.getLastDataRowNum(); i++) {
			int numH = i+1;
			QuestionModel q = new QuestionModel();
			q.setId(GlobalUtil.createGlobalId());
			q.setQuestionNo(String.valueOf(qno++));
			Row row = this.getRow(i);
			Object val = this.getCellValue(row, 0);
			String sc = String.valueOf(val);
			if(StringUtils.isEmpty(sc)){
				continue;
			}
			if(!NumberUtils.isNumber(sc)){
				throw new RuntimeException("文档格式不正确!");
			}
			//设置知识点
			Object pointName = this.getCellValue(row, 1);
			String[] pNames = null;
			String[] pRa = null;
			if(pointName!=null){
				String pointNameStr = String.valueOf(pointName);
				if(!StringUtils.isEmpty(pointNameStr)){
					pNames = pointNameStr.split("<>");
					//设置知识点得分比
					Object pointRate = this.getCellValue(row, 2);
					if(pointRate!=null){
						String pointRateStr = String.valueOf(pointRate);
						if(!StringUtils.isEmpty(pointRateStr)){
							pRa = pointRateStr.split("<>");
							if(pNames.length!=pRa.length){
								throw new RuntimeException("导入失败！请检查第："+numH+"行知识点,占比设置。如不清楚，请下载模板参考或请联系管理员。");
							}
							//遍历知识点占比数据。保证所有占比相加等于100,切都为数字
							int num = 0;
							for(int ii = 0 ;ii<pRa.length;ii++){
								if(!NumberUtils.isNumber(pRa[ii])){
									throw new RuntimeException("请检查第："+numH+"行。知识点占比为0到100的整数!");
								}
								num = num + Integer.parseInt(pRa[ii]);
							}
							if(num!=100){
								throw new RuntimeException("请检查第："+numH+"行。知识点占比之和必须为100!");
							}
						}
					}
					if(pRa==null||pRa.length==0){
						//自动设置知识点占比
						String[] nP = new String[pNames.length];
						BigDecimal[] avgRate = new BigDecimal(100).divideAndRemainder(new BigDecimal(pNames.length));
						Integer newRare = avgRate[0].intValue();
						Integer mo = avgRate[1].intValue();
						for(int ii=0;ii<pNames.length;ii++){
							Integer sheR = newRare;
							if(mo>0){
								sheR++;
								mo--;
							}
							nP[ii] = sheR.toString();
						}
						pRa = nP;
					}
				}
			}
			Boolean isSu = setQuesPoint(q,pNames,pRa,poList);
			if(!isSu){
				throw new RuntimeException("导入失败！请检查第："+numH+"行知识点设置。如不清楚，请下载模板参考或请联系管理员。");
			}
			q.setScore(sc);
			reList.add(q);
		}
		return reList;
	}

	/**
	 * 针对考试设置知识点
	 */
	public Boolean setQuesPoint(QuestionModel q,String[] pNames,String[] pRa,Map<String,PointPaperModel> poList) throws Exception{
		//未设置知识点的！就设置一个默认知识点
		if(pNames==null||pNames.length==0){
			List<PointPaperModel> inPo = new ArrayList<>();
			//随机设置一个知识点
			PointPaperModel p = getFirstOrNull(poList);
			p.setId(GlobalUtil.createGlobalId());
			p.setProportion("100");
			inPo.add(p);
			q.setPointPaperListVo(inPo);
		}
		else{
			if(pRa ==null||pRa.length!=pNames.length){
				return false;
			}
			//验证知识点名称
			List<String> pList = Arrays.asList(pNames);
			List<PointPaperModel> inPo = new ArrayList<>();
			int i = 0;
			for (String s:pList) {
				String k = Encodes.md5(s);
				//验证知识点
				if(poList.containsKey(k)){
					PointPaperModel pp = poList.get(k);
					PointPaperModel p = new PointPaperModel();
					BeanUtils.copyProperties(pp,p);
					p.setId(GlobalUtil.createGlobalId());
					p.setProportion(pRa[i]);
					inPo.add(p);
					i++;
				}
				else{
					return false;
				}
			}
			q.setPointPaperListVo(inPo);
		}
		return true;
	}

	public PointPaperModel getFirstOrNull(Map<String, PointPaperModel> map) {
		PointPaperModel obj = null;
		for (Map.Entry<String, PointPaperModel> entry : map.entrySet()) {
			obj = entry.getValue();
			if (obj != null) {
				break;
			}
		}
		return obj;
	}

	/**
	 *  导入成绩
	 * @return 成绩列表
	 */
	public ScoreReceverModel getScoreReceverModel(ScoreReceverModel scoreReceverModel){
		List<StuScoreReceverModel> scoreList = new ArrayList<>();
		//遍历数据
		for (int i = this.getDataRowNum(); i <= this.getLastDataRowNum(); i++) {
			StuScoreReceverModel stuScoreReceverModel = new StuScoreReceverModel();
			stuScoreReceverModel.setId(GlobalUtil.createGlobalId());
			Row row = this.getRow(i);
			//获取所有的列数据
			List<Double> scoreLists = new ArrayList<>();
			for (int j=0;j<this.getLastCellNum();j++){
				Object val = this.getCellValue(row, j);
				switch (j){
					case 0:stuScoreReceverModel.setStudentNo(String.valueOf(val.toString()));
						break;
					case 1:stuScoreReceverModel.setStudentName(String.valueOf(val.toString()));
						break;
				}
				if(j>1 && StringUtils.isNotBlank(val+"")){
					scoreLists.add(Double.parseDouble(val+""));
				}
			}
			stuScoreReceverModel.setScoreDetailList(scoreLists);
			if(StringUtils.isNotBlank(stuScoreReceverModel.getStudentNo()))
				scoreList.add(stuScoreReceverModel);
		}
		scoreReceverModel.setScoreDetailList(scoreList);
		return scoreReceverModel;
	}
}
